﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Text.RegularExpressions;


namespace semanticdb
{
	public partial class Form1 : Form
	{
		SqlConnection Con = new SqlConnection(@"Data Source=1-ПК\MSSQLSERVER2;Initial Catalog=semantic_db;Integrated Security=True");
		SqlDataAdapter da = new SqlDataAdapter();
		
		public Form1()
		{
			InitializeComponent();
		}
		
		private void Form1_Load(object sender, EventArgs e)
		{
			// TODO: This line of code loads data into the 'semantic_dbDataSet.slovar' table. You can move, or remove it, as needed.
			this.slovarTableAdapter.Fill(this.semantic_dbDataSet.slovar);

		}

		/// <summary>
		/// Добавление статьи в БД
		/// </summary>
		/// <param name="tezaurus">Статья</param>
		private void add_DB_tezaurus(String tezaurus)
		{
			
 
			String[] words = { null };
			words = Regex.Split(tezaurus, @"\W*(\w+)");
			int i = 0;
			int cont_id = 0;
			SqlDataReader Dr = null;
			int num = 0;
            String first_word = null;
			foreach (string item in words)
			{
				if (words[i] != "" && words[i] != "." && words[i] != "!" && words[i] != "?" && words[i] != "")
				{
					if (words[i].Length > 4) //Для слов длинее 4-х букв
					{
						String temp = words[i];
						words[i] = null;
						for (int j = 0; j < temp.Length / 2 + 2; j++)
							words[i] += temp[j];
						String temp_cmd = null;


                        if (cont_id == 0)
                        {
                            first_word = words[i];
                            da.InsertCommand = new SqlCommand("SELECT COUNT(pf) FROM slovar WHERE pf = '" + words[i] + "'", Con);
                            Con.Open();
                            Dr = da.InsertCommand.ExecuteReader();
                            Dr.Read();
                            num = Dr.GetInt32(0);
                            Con.Close();
                            if (num != 0) return;
                            da.InsertCommand = new SqlCommand("INSERT INTO slovar (pf, num) VALUES (@PF, @NUM) INSERT INTO pf_tezaurus (id_pf0) SELECT MAX(id_pf) FROM slovar ", Con);
                            da.InsertCommand.Parameters.Add("@PF", SqlDbType.NChar).Value = words[i];
                            da.InsertCommand.Parameters.Add("@NUM", SqlDbType.Int).Value = 1;
                        }
                        else
                        {
                            da.InsertCommand = new SqlCommand("SELECT COUNT(pf) FROM slovar WHERE pf = '" + words[i] + "'", Con);
                            Con.Open();
                            Dr = da.InsertCommand.ExecuteReader();
                            Dr.Read();
                            num = Dr.GetInt32(0);
                            Con.Close();
                            Dr.Close();
                            if (num == 0)
                            {
                                da.InsertCommand = new SqlCommand("INSERT INTO slovar (pf, num) VALUES (@PF, @NUM) UPDATE pf_tezaurus SET id_pf" + cont_id + " = (SELECT MAX(id_pf) FROM slovar) WHERE id_pf0 = (SELECT id_pf FROM slovar WHERE pf = @FW)", Con);
                                da.InsertCommand.Parameters.Add("@PF", SqlDbType.NChar).Value = words[i];
                                da.InsertCommand.Parameters.Add("@NUM", SqlDbType.Int).Value = 1;
                                da.InsertCommand.Parameters.Add("@FW", SqlDbType.NChar).Value = first_word;
                            }
                            else
                            {
                                da.InsertCommand = new SqlCommand("SELECT id_pf FROM slovar WHERE pf=@PF", Con);
                                da.InsertCommand.Parameters.Add("@PF", SqlDbType.NChar).Value = words[i];
                                Con.Open();
                                Dr = da.InsertCommand.ExecuteReader();
                                Dr.Read();
                                int n = Dr.GetInt32(0);
                                Con.Close();

                                da.InsertCommand = new SqlCommand("UPDATE slovar SET num = 1+(SELECT num FROM slovar WHERE id_pf = @ID) WHERE id_pf = @ID", Con);
                                da.InsertCommand.Parameters.Add("@ID", SqlDbType.Int).Value = n;
                                Con.Open();
                                Dr = da.InsertCommand.ExecuteReader();
                                Con.Close();

                                da.InsertCommand = new SqlCommand("UPDATE pf_tezaurus SET id_pf" + cont_id + " = @ID WHERE id_pf0 = (SELECT id_pf FROM slovar WHERE pf = @FW)", Con);
                                da.InsertCommand.Parameters.Add("@ID", SqlDbType.Int).Value = n;
                                da.InsertCommand.Parameters.Add("@FW", SqlDbType.NChar).Value = first_word;
                            }
                        }
                        Con.Open();
                        da.InsertCommand.ExecuteReader();
                        Con.Close();

                        cont_id++;
					}
					else //для слов короче 4-х букв
					{
                        if (cont_id == 0)
                        {
                            first_word = words[i];
                            da.InsertCommand = new SqlCommand("SELECT COUNT(pf) FROM slovar WHERE pf = '" + words[i] + "'", Con);
                            Con.Open();
                            Dr = da.InsertCommand.ExecuteReader();
                            Dr.Read();
                            num = Dr.GetInt32(0);
                            Con.Close();
                            if (num != 0) return;
                            da.InsertCommand = new SqlCommand("INSERT INTO slovar (pf, num) VALUES (@PF, @NUM) INSERT INTO pf_tezaurus (id_pf0) SELECT MAX(id_pf) FROM slovar ", Con);
                            da.InsertCommand.Parameters.Add("@PF", SqlDbType.NChar).Value = words[i];
                            da.InsertCommand.Parameters.Add("@NUM", SqlDbType.Int).Value = 1;
                        }
                        else
                        {
                            da.InsertCommand = new SqlCommand("SELECT COUNT(pf) FROM slovar WHERE pf = '" + words[i] + "'", Con);
                            Con.Open();
                            Dr = da.InsertCommand.ExecuteReader();
                            Dr.Read();
                            num = Dr.GetInt32(0);
                            Con.Close();
                            Dr.Close();
                            if (num == 0)
                            {
                                da.InsertCommand = new SqlCommand("INSERT INTO slovar (pf, num) VALUES (@PF, @NUM) UPDATE pf_tezaurus SET id_pf" + cont_id + " = (SELECT MAX(id_pf) FROM slovar) WHERE id_pf0 = (SELECT id_pf FROM slovar WHERE pf = @FW)", Con);
                                da.InsertCommand.Parameters.Add("@PF", SqlDbType.NChar).Value = words[i];
                                da.InsertCommand.Parameters.Add("@NUM", SqlDbType.Int).Value = 1;
                                da.InsertCommand.Parameters.Add("@FW", SqlDbType.NChar).Value = first_word;
                            }
                            else
                            {
                                da.InsertCommand = new SqlCommand("SELECT id_pf FROM slovar WHERE pf=@PF", Con);
                                da.InsertCommand.Parameters.Add("@PF", SqlDbType.NChar).Value = words[i];
                                Con.Open();
                                Dr = da.InsertCommand.ExecuteReader();
                                Dr.Read();
                                int n = Dr.GetInt32(0);
                                Con.Close();

                                da.InsertCommand = new SqlCommand("UPDATE slovar SET num = 1+(SELECT num FROM slovar WHERE id_pf = @ID) WHERE id_pf = @ID", Con);
                                da.InsertCommand.Parameters.Add("@ID", SqlDbType.Int).Value = n;
                                Con.Open();
                                Dr = da.InsertCommand.ExecuteReader();
                                Con.Close();

                                da.InsertCommand = new SqlCommand("UPDATE pf_tezaurus SET id_pf" + cont_id + " = @ID WHERE id_pf0 = (SELECT id_pf FROM slovar WHERE pf = @FW)", Con);
                                da.InsertCommand.Parameters.Add("@ID", SqlDbType.Int).Value = n;
                                da.InsertCommand.Parameters.Add("@FW", SqlDbType.NChar).Value = first_word;
                            }
                        }
						Con.Open();
						da.InsertCommand.ExecuteReader();
						Con.Close();

						cont_id++;
					}
					MessageBox.Show(words[i]);
				}
				i++;
			}


		}
		private void batton_add_tezaurus_Click(object sender, EventArgs e)
		{
			String tezaurus = null;
			tezaurus = this.text_add_tezaurus.Text;
			this.text_test.Text = tezaurus;
			add_DB_tezaurus(tezaurus);
		}

		private void saveToolStripButton_Click(object sender, EventArgs e)
		{
			this.slovarTableAdapter.Update(this.semantic_dbDataSet);
		}
		static public string ReverseMe(string strSample)
		{
			char[] charArray = strSample.ToCharArray();
			Array.Reverse(charArray);
			return new string(charArray);
		}

		/// <summary>
		/// Поиск слов в БД
		/// </summary>
		/// <param name="search">Строка поиска</param>
		private void search_resault(String search)
		{
			String[] words = { null };
			words = Regex.Split(search, @"\W*(\w+)");
			int i = 0;
			int cont_id = 0;
			String[] id_string = { null };
			int[] id_int = new int[32]; 
			SqlDataReader Dr = null;
			foreach (string item in words)
			{
				if (words[i] != "" && words[i] != "." && words[i] != "!" && words[i] != "?" && words[i] != "")
				{
					if ((words[i].Length) > 4)
					{
						String temp = words[i];
						words[i] = null;
						for (int j = 0; j < temp.Length / 2 + 2; j++)
							words[i] += temp[j];

						da.InsertCommand = new SqlCommand("SELECT id_pf FROM slovar WHERE pf = @PF",Con);
						da.InsertCommand.Parameters.Add("@PF", SqlDbType.NChar).Value = words[i];
						
						Con.Open();
						Dr = da.InsertCommand.ExecuteReader();

						String str = null;
						int id;
						while (Dr.Read())
						{
							str = Dr[0].ToString();
						}

						Con.Close();
						int.TryParse(str, out id);

						da.InsertCommand = new SqlCommand(@"SELECT *  FROM pf_tezaurus WHERE id_pf0 = @ID_PF", Con);
						da.InsertCommand.Parameters.Add("@ID_PF", SqlDbType.Int).Value = id;
						Con.Open();
						Dr = da.InsertCommand.ExecuteReader();
						int count = 0;
						Dr.Read();
						int n = Dr.FieldCount;
						 while (count < n)
						 {
							if (Dr.IsDBNull(count)) break;
							id_int[count] = Dr.GetInt32(count);
							count++;
						 }
						 String result_string = null;
						Con.Close();
						n = count;
						count = 0;
						while (count < n)
						{
							
							da.InsertCommand = new SqlCommand(@"SELECT pf  FROM slovar WHERE id_pf = @ID_PF", Con);
							da.InsertCommand.Parameters.Add("@ID_PF", SqlDbType.Int).Value = id_int[count];
							Con.Open();
							Dr = da.InsertCommand.ExecuteReader();
							while (Dr.Read())
							{
								str = Dr[0].ToString();
							}
							str = str.Replace(" ", string.Empty);
							result_string +=" "+ str;
							
							count++;
							Con.Close();
						}
							this.lst_search.Items.Add(result_string);
						   

					}
				}
				i++;
			}
		}
		private void btn_search_Click(object sender, EventArgs e)
		{
			String search = null;
			search = this.txt_search.Text;
			this.lst_search.Items.Clear();
			search_resault(search);
			
		}
	}
}
